package com.hnkywl.sts.dao.organ;

import com.hnkywl.sts.entity.organ.Bmfw;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: ccr
 * Date: 13-5-20
 * Time: 下午7:34
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class BmfwDao extends SimpleHibernateTemplate<Bmfw,Long>{
    public List<Map> findTreejson(Long gsId,Integer type,Long sjwzbmId,String bm,String sjwzbmbm){
        String select = "select wzbm.id as \"wzbmId\",wzbm.name as \"name\",bm.bmz as \"code\""
                +(bm.equalsIgnoreCase("pm")?",wzbm.gysmc as \"gysmc\"":"");
        String from = " from t_sys_bmfw bmfw left join t_sys_@bm wzbm on (bmfw.wzbmid = wzbm.id) " +
                "left join t_sys_bmb bm on(wzbm.code = bm.bmz) left join t_sys_bmlx bmlx on(bm.bmlxid = bmlx.id)";
        if(gsId == null){
            from = "from t_sys_@bm wzbm "+
                    "left join t_sys_bmb bm on(wzbm.code = bm.bmz) left join t_sys_bmlx bmlx on(bm.bmlxid = bmlx.id)";
        }
        StringBuilder where = new StringBuilder();
        where.append("bm.bmz is not null and lower(bmlx.lxzjm) = '@bm' and bm.zt = '1'");
        if(gsId != null) {
            if(where.length()!=0)
                where.append(" and ");
            where.append("bmfw.gsId = :gsId");
        }
        if(type!=null){
            if(where.length()!=0)
                where.append(" and ");
            where.append("bmfw.type = :type");
        }
        if(sjwzbmId!=null && sjwzbmId!=0){
            if(where.length()!=0)
                where.append(" and ");
            where.append("wzbm.@sjbm = :sjwzbmId");
        }
        String orderBy = " order by bm.bmz asc";
        String queryString = select+from+(where.length()!=0?" where ":"")+where + orderBy;
        queryString = queryString.replaceAll("@bm",bm).replaceAll("@sjbm",sjwzbmbm);
        SQLQuery query = getSession().createSQLQuery(queryString);
        if(gsId != null) {
            query.setLong("gsId",gsId);
        }
        if(type!=null){
            query.setInteger("type",type);
        }
        if(sjwzbmId!=null && sjwzbmId!=0){
            query.setLong("sjwzbmId",sjwzbmId);
        }
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return query.list();
    }

    public void deleteByGsId(Long gsId){
        String hql = "delete from Bmfw where gsId = :gsId";
        Query query = getSession().createQuery(hql);
        query.setParameter("gsId",gsId).executeUpdate();
    }

    public boolean existChildren(Long gsId,Integer type,Long sjwzbmId,String bm,String sjwzbmbm){
        String select = "select bmfw.* ";
        String from = " from t_sys_bmfw bmfw left join t_sys_@bm wzbm on (bmfw.wzbmid = wzbm.id)";
        String where = "where bmfw.gsId = ? and bmfw.type = ? and wzbm.@sjbm = ?";
        String sql = select+from+where;
        sql = sql.replaceAll("@bm",bm).replaceAll("@sjbm",sjwzbmbm);
        return existsSQLQuery(sql,gsId,type,sjwzbmId);
    }


    public List<Map> findTreejsonByYg(Long ygId,Integer type,Long sjwzbmId,String bm,String sjwzbmbm){
        String select = "SELECT A.* FROM (SELECT DISTINCT @WZBM.ID, @WZBM.NAME, @WZBM.CODE " +
                " FROM T_SYS_YJWZBM Y, T_SYS_RJWZBM R, T_SYS_PM P, T_SYS_YG_BMFW B " +
                " WHERE Y.ID = R.YJWZBM_ID AND R.ID = P.RJWZBM_ID AND P.ID = B.PM_ID " +
                " AND Y.DELETED = 0 AND R.DELETED = 0 AND P.DELETED = 0 AND B.YG_ID = :ygId " +
                (sjwzbmId == null ? "" : "AND @SJWZBM = :sjwzbmId") + " ) A, T_SYS_BMB BM " +
                " WHERE A.CODE = BM.BMZ AND BM.ZT = 1 ORDER BY A.CODE";
        String queryString = select.replaceAll("@WZBM",bm).replaceAll("@SJWZBM",sjwzbmbm);
        SQLQuery query = getSession().createSQLQuery(queryString);
        query.setLong("ygId",ygId);

        if(sjwzbmId!=null && sjwzbmId!=0){
            query.setLong("sjwzbmId",sjwzbmId);
        }
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return query.list();
    }
}
